---
sidebar_label: Computed fields
sidebar_position: 4.2
description: Use computed fields over Postgres in Hasura
keywords:
  - hasura
  - docs
  - postgres
  - schema
  - computed field
---

import GraphiQLIDE from '@site/src/components/GraphiQLIDE';
import Thumbnail from '@site/src/components/Thumbnail';
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

# Postgres: Computed Fields

## What are computed fields?

Computed fields are virtual values or objects that are dynamically computed and can be queried along with a table/view's
columns.

Computed fields are computed upon request. They are computed by executing
[user-defined SQL functions](https://www.postgresql.org/docs/current/sql-createfunction.html) (a.k.a. stored procedures)
which take the columns of the table/view, and other custom values if needed, as inputs to compute the field.

:::info Computed fields do not modify the database schema

Computed fields are only exposed over the GraphQL API and the database schema is not modified on addition of a computed
field. i.e. a computed field cannot be fetched as a table column directly from the database.

:::

### Supported SQL functions

Only functions which satisfy the following constraints can be added as a computed field to a table/view. (_terminology
from_ [Postgres docs](https://www.postgresql.org/docs/current/sql-createfunction.html)):

- **Function behavior**: ONLY `STABLE` or `IMMUTABLE`
- **Argument modes**: ONLY `IN`
- **Table Argument**: ONE input argument as the table row type
- **Return type**: Either `SETOF <table-name>` or `BASE` type

Functions used as computed fields can also accept other arguments other than the mandatory table row argument. Values
for these extra arguments can be passed as arguments to the computed field in the GraphQL API.

:::info No need for tracking

Functions used as computed fields do not need to be [tracked](./custom-functions.mdx#pg-track-custom-sql-functions) by
the Hasura GraphQL Engine. As the requirements for tracking functions differ from those to add them as computed fields,
not all functions that can be used as computed fields can be tracked and vice versa.

:::

## Computed field types

Based on the SQL function's return type, we can define two types of computed fields:

### 1. Scalar computed fields

Computed fields whose associated SQL function returns a
[base type](https://www.postgresql.org/docs/current/extend-type-system.html#id-1.8.3.5.9) like _Integer_, _Boolean_,
_Geography_ etc. are scalar computed fields.

**Example:**

Let's say we have the following schema:

```plsql
authors(id integer, first_name text, last_name text)
```

[Define an SQL function](/schema/postgres/custom-functions.mdx#pg-create-sql-functions) called `author_full_name`:

```plsql
CREATE FUNCTION author_full_name(author_row authors)
RETURNS TEXT AS $$
  SELECT author_row.first_name || ' ' || author_row.last_name
$$ LANGUAGE sql STABLE;
```

[Add a computed field](#pg-adding-computed-field) called `full_name` to the `authors` table using the SQL function
above.

Query data from the `authors` table:

<GraphiQLIDE
  query={`query {
  authors {
    id
    first_name
    last_name
    full_name
  }
}`}
  response={`{
  "data": {
    "authors": [
      {
        "id": 1,
        "first_name": "Chris",
        "last_name": "Raichael",
        "full_name": "Chris Raichael"
      }
    ]
  }
}`}
/>

### 2. Table computed fields

Computed fields whose associated SQL function returns `SETOF <table-name>` are table computed fields. The return table
must be tracked to define such a computed field.

**Example:**

Let's say we have the following schema:

```plsql
authors(id integer, first_name text, last_name text)

articles(id integer, title text, content text, author_id integer)
```

Now we can define a [table relationship](/schema/postgres/table-relationships/index.mdx) on the `authors` table to fetch
authors along with their articles.

We can make use of computed fields to fetch the author's articles with a search parameter.

[Define an SQL function](/schema/postgres/custom-functions.mdx#pg-create-sql-functions) called `filter_author_articles`:

```plsql
CREATE FUNCTION filter_author_articles(author_row authors, search text)
RETURNS SETOF articles AS $$
  SELECT *
  FROM articles
  WHERE
    ( title ilike ('%' || search || '%')
      OR content ilike ('%' || search || '%')
    ) AND author_id = author_row.id
$$ LANGUAGE sql STABLE;
```

[Add a computed field](#pg-adding-computed-field) called `filtered_articles` to the `authors` table using the SQL
function above.

Query data from the `authors` table:

<GraphiQLIDE
  query={`query {
  authors {
    id
    first_name
    last_name
    filtered_articles(args: {search: "Hasura"}){
      id
      title
      content
    }
  }
}`}
  response={`{
  "data": {
    "authors": [
      {
        "id": 1,
        "first_name": "Chris",
        "last_name": "Raichael",
        "filtered_articles": [
          {
            "id": 1,
            "title": "Computed fields in Hasura",
            "content": "lorem ipsum dolor sit amet"
          }
        ]
      }
    ]
  }
}`}
/>

## Adding a computed field to a table/view {#pg-adding-computed-field}

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

Head to the `Modify` tab of the table/view and click on the `Add` button in the `Computed fields` section:

<Thumbnail src="/img/schema/computed-field-create.png" alt="Add computed field" width="750px" />

</TabItem>
<TabItem value="cli" label="CLI">

You can add a computed field by updating the `metadata > databases > [db-name] > tables > [table_name].yaml` file:

```yaml {4-11}
- table:
    schema: public
    name: authors
  computed_fields:
    - name: full_name
      definition:
        function:
          schema: public
          name: author_full_name
        table_argument: null
      comment: ''
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

A computed field can be added to a table/view using the
[pg_add_computed_field](/api-reference/metadata-api/computed-field.mdx#metadata-pg-add-computed-field) Metadata API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "pg_add_computed_field",
  "args": {
    "source": "<db_name>",
    "table": {
      "name": "authors",
      "schema": "public"
    },
    "name": "full_name",
    "definition": {
      "function": {
        "name": "author_full_name",
        "schema": "public"
      },
      "table_argument": "author_row"
    }
  }
}
```

</TabItem>
</Tabs>

## Computed fields permissions

[Access control](/auth/authorization/index.mdx) to computed fields depends on the type of computed field.

- For **scalar computed fields**, permissions are managed similar to the
  [columns permissions](/auth/authorization/permissions/column-level-permissions.mdx) of the table.
- For **table computed fields**, the permissions set on the return table are respected.

## Accessing Hasura session variables in computed fields

It can be useful to have access to the session variable from the SQL function defining a computed field. For instance,
suppose we want to record which users have liked which articles. We can do so using a table `article_likes` that
specifies a many-to-many relationship between `articles` and `users`. In such a case it can be useful to know if the
current user has liked a specific article, and this information can be exposed as a _Boolean_ computed field on
`articles`.

Create a function with an argument for session variables and add it with the
[pg_add_computed_field](/api-reference/metadata-api/computed-field.mdx#metadata-pg-add-computed-field) API with the
`session_argument` key set. The session argument is a JSON object where keys are session variable names (in lower case)
and values are strings. Use the `->>` JSON operator to fetch the value of a session variable as shown in the following
example.

```plsql
-- 'hasura_session' will be the session argument
CREATE OR REPLACE FUNCTION article_liked_by_user(article_row articles, hasura_session json)
RETURNS boolean AS $$
SELECT EXISTS (
    SELECT 1
    FROM article_likes A
    WHERE A.user_id = hasura_session ->> 'x-hasura-user-id' AND A.article_id = article_row.id
);
$$ LANGUAGE sql STABLE;
```

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
    "type":"pg_add_computed_field",
    "args":{
        "source": "<db_name>",
        "table":{
            "name":"articles",
            "schema":"public"
        },
        "name":"liked_by_user",
        "definition":{
            "function":{
                "name":"article_liked_by_user",
                "schema":"public"
            },
            "table_argument":"article_row",
            "session_argument":"hasura_session"
        }
    }
}
```

<GraphiQLIDE
  query={`query {
  articles(where: {id: {_eq: 3}}) {
    id
    liked_by_user
  }
}`}
  response={`{
 "data": {
   "articles": [
     {
       "id": "3",
       "liked_by_user": true
     }
   ]
 }
}`}
/>

:::info Note

The specified session argument is not included in the argument options of the computed field in the GraphQL schema.

:::

:::tip Supported from

This feature is available in `v1.3.0` and above

:::

## Computed fields vs. Postgres generated columns

Postgres, from version `12`, is introducing
[Generated Columns](https://www.postgresql.org/docs/12/ddl-generated-columns.html). The value of generated columns is
also computed from other columns of a table. Postgres' generated columns come with their own limitations. Hasura's
computed fields are defined via an SQL function, which allows users to define any complex business logic in a function.
Generated columns will go together with computed fields where Hasura treats generated columns as normal Postgres
columns.

## Computed fields in Remote Relationships

Using computed fields in [Remote Relationships](/schema/postgres/remote-relationships/index.mdx) allows transformation
of data from table columns before joining with data from remote sources. For instance, suppose we want to extract
certain field from a `json` column and join it with a field in a Remote Schema by argument value. We would define a
computed field which returns a scalar type of the field value in the `json` column and use it to join the graphql field
of the Remote Schema. Consider the following Postgres schema.

<Thumbnail src="/img/databases/postgres/schema/computed-fields-remote-relationship.png" width="700px" />

```plsql
CREATE TABLE "user" (id SERIAL PRIMARY KEY, name TEXT UNIQUE NOT NULL, address json NOT NULL);

-- SQL function returns city of a "user" using "->>" json operator
CREATE FUNCTION get_city(table_row "user")
RETURNS TEXT AS $$
  SELECT table_row.address ->> 'city'
$$ LANGUAGE sql STABLE;
```

Now, let's track the table and add computed field `user_city` using the SQL function `get_city`. Consider the following
Remote Schema.

```graphql
type Query {
  get_coordinates(city: String): Coordinates
}
type Coordinates {
  lat: Float
  long: Float
}
```

[Define a remote relationship](/api-reference/metadata-api/remote-relationships.mdx#metadata-pg-create-remote-relationship)
with name `user_location` from `user_city` scalar computed field to `get_coordinates` Remote Schema field. We can query
users with the pincode of their residing place.

<GraphiQLIDE
  query={`query {
  user {
    id
    name
    user_city
    user_location
  }
}`}
  response={`{
  "data": {
    "authors": [
      {
        "id": 1,
        "name": "Alice",
        "user_city": "Frisco",
        "user_location": {
          "lat": 33.155373,
          "long": -96.818733
        }
      }
    ]
  }
}`}
/>

:::info Note

Only `Scalar computed fields` are allowed to join fields from remote sources

:::

:::tip Supported from

This feature is available in `v2.0.1` and above

:::
